﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace Poligono.Core.Exceptions
{
    /// <summary>
    /// Class that will do the exception handling SQL Server database
    /// </summary>
    public class SqlServerException
    {
        #region Constants
        private const int PRIMARY_KEY_NUMBER = 2627;
        private const string PRIMARY_KEY_MESSAGE = "Violation of PRIMARY KEY constraint";

        private const int UNIQUE_KEY_NUMBER = 2601;
        private const string UNIQUE_KEY_MESSAGE = "with unique index";

        private const int FOREIGN_KEY_INSERT_NUMBER = 547;
        private const string FOREIGN_KEY_INSERT_MESSAGE = "The INSERT statement conflicted with the FOREIGN KEY constraint";

        private const int FOREIGN_KEY_DELETE_NUMBER = 547;
        private const string FOREIGN_KEY_DELETE_MESSAGE = "The DELETE statement conflicted with the REFERENCE constraint";
        #endregion Constants

        /// <summary>
        /// Method to handle the exception
        /// </summary>
        /// <param name="excecao">Exception that has been passed to the class</param>
        /// <param name="message">Message that will be returned to the application that make the call to the class</param>
        /// <param name="path">Screen that fired the error</param>
        /// <returns>Indicating whether the exception was handled successfully or not</returns>
        public static bool Treat(Exception exception, out string message, string path)
        {
            message = string.Empty;
            try
            {
                // If the exception is not SQLExcpetion, but has an InnerException of type SqlException
                if (exception is SqlException || exception.InnerException is SqlException)
                {
                    SqlException sqlException = exception is SqlException ? (SqlException)exception : (SqlException)exception.InnerException;
                    return TreatSqlException(sqlException, out message, path);
                }
                else
                {
                    return false;
                }
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// SqlException exception handling
        /// </summary>
        /// <param name="exception">Exception that has been passed to the class</param>
        /// <param name="message">Message that will be returned to the application that make the call to the class</param>
        /// <param name="path">Tela que iniciou o erro</param>
        /// <returns></returns>
        public static bool TreatSqlException(SqlException exception, out string message, string path)
        {
            message = string.Empty;
            try
            {
                // Getting the name of the constraint
                string constraint = GetConstraintName(exception.Message, exception.Number);
                // Getting the message for the constraint
                message = GetConstraintMessage(constraint, GetConstraintType(exception.Message, exception.Number));
                if (string.IsNullOrEmpty(message))
                {
                    message = GetDefaultConstraintMessage(exception);
                }
                if (!string.IsNullOrEmpty(message))
                {
                    return true;
                }
                return false;
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// Get (generate) the default message for the exception
        /// </summary>
        /// <param name="exception">Exception</param>
        /// <returns>Default message for the exception</returns>
        public static string GetDefaultConstraintMessage(SqlException exception)
        {
            string message = null;
            switch (exception.Number)
            {
                case 2627:
                    message = GetConstraintMessage("PRIMARY_KEY", ConstraintErrorTypeSqlServer.PrimaryKey);
                    break;
                case 2601:
                    message = GetConstraintMessage("UNIQUE_KEY", ConstraintErrorTypeSqlServer.UniqueKey);
                    break;
                case 547:
                    message = GetConstraintMessage("FOREIGN_KEY", ConstraintErrorTypeSqlServer.NA);
                    break;
                case 50000:
                    message = exception.Message;//Exceptions generated on sql using RAISERROR (See the SqlServer help)
                    break;
            }
            return message;
        }

        /// <summary>
        /// Get the message stored on database for the constraint provided
        /// Table: ConstrInfo
        /// </summary>
        /// <param name="constraint">Constraint name</param>
        /// <returns>Constraint message</returns>
        /// <param name="type">Constraint type</param>
        public static string GetConstraintMessage(string constraint, ConstraintErrorTypeSqlServer type)
        {
            #region SQL
            string sql = @"SELECT Ctr_Descricao
                       FROM tb_ConstrInfo
                       WHERE Ctr_Nome = @Ctr_Nome AND
                        ( Ctr_Tipo = @Ctr_Tipo OR Ctr_Tipo is null )";
            #endregion SQL

            //BdUtil bd = new BdUtil(sql);
            //bd.AdicionarParametro("@Ctr_Nome", DbType.AnsiString, constraint);
            //bd.AdicionarParametro("@Ctr_Tipo", DbType.AnsiString, tipo.ToString());
            //DataTable dt = bd.ObterDataTable();
            //if (dt.Rows.Count > 0)
            //{
            //    return dt.Rows[0]["Ctr_Descricao"].ToString();
            //}
            //else
            //{
            //    return null;
            //}
            return string.Empty;
        }

        /// <summary>
        /// Get the constraint name contained on the exception message
        /// </summary>
        /// <param name="message">Exception message</param>
        /// <param name="number">exception number returned by Sql Server</param>
        /// <returns>Constraint name</returns>
        private static string GetConstraintName(string message, int number)
        {
            string constraint = null;
            if (number == PRIMARY_KEY_NUMBER)
            {
                constraint = ExtractConstraintName(PRIMARY_KEY_MESSAGE, message, '\'');
                constraint = constraint.Replace("\'", "").Trim();
            }
            else if (number == UNIQUE_KEY_NUMBER)
            {
                constraint = ExtractConstraintName(UNIQUE_KEY_MESSAGE, message, '\'');
                constraint = constraint.Replace("\'", "").Trim();
            }
            else if (number == FOREIGN_KEY_INSERT_NUMBER && message.Contains("INSERT"))
            {
                constraint = ExtractConstraintName(FOREIGN_KEY_INSERT_MESSAGE, message, '\"');
                constraint = constraint.Replace("\"", "").Trim();
            }
            else if (number == FOREIGN_KEY_DELETE_NUMBER && message.Contains("DELETE"))
            {
                constraint = ExtractConstraintName(FOREIGN_KEY_DELETE_MESSAGE, message, '\"');
                constraint = constraint.Replace("\"", "").Trim();
            }
            return constraint;
        }
        
        /// <summary>
        /// Get the constraint error type (Primary Key, Unique Key, Foreign Key)
        /// </summary>
        /// <param name="message">Error message fired by the framework .net</param>
        /// <param name="number">number error code</param>
        /// <returns>returns the constraint error type</returns>
        private static ConstraintErrorTypeSqlServer GetConstraintType(string message, int number)
        {
            if (message.Contains(PRIMARY_KEY_MESSAGE))
            {
                return ConstraintErrorTypeSqlServer.PrimaryKey;
            }
            else if (message.Contains(UNIQUE_KEY_MESSAGE) || message.Contains("Violation of UNIQUE KEY constraint") || message.Contains("Violação da restrição UNIQUE KEY "))
            {
                return ConstraintErrorTypeSqlServer.UniqueKey;
            }
            else if (number == FOREIGN_KEY_INSERT_NUMBER && message.Contains("INSERT"))
            {
                return ConstraintErrorTypeSqlServer.FK_Insert;
            }
            else if (number == FOREIGN_KEY_DELETE_NUMBER && message.Contains("DELETE"))
            {
                return ConstraintErrorTypeSqlServer.FK_Delete;
            }
            else
                return ConstraintErrorTypeSqlServer.NA;
        }

        /// <summary>
        /// Extracts the constraint name
        /// </summary>
        /// <param name="initialMessage">initial part (fixed) of the message</param>
        /// <param name="message">Exception message</param>
        /// <param name="delimiter">delimiter name of the constraint exception message</param>
        /// <returns>Constraint name</returns>
        private static string ExtractConstraintName(string initialMessage, string message, char delimiter)
        {
            int inicialConstraint = message.IndexOf(initialMessage) + initialMessage.Length;
            int finalConstraint = message.IndexOf('.', inicialConstraint);
            return message.Substring(inicialConstraint, finalConstraint - inicialConstraint);
        }
    }
}